Imports Microsoft.VisualBasic
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports EntityLayer

Public Class ProductoDA

    Public Shared Function ListarProductos() As List(Of Producto)

        Dim myConnection As New SqlConnection(ConexionDA.ObtenerCnx)
        Dim myCommand As New SqlCommand("usp_ListProductos", myConnection)
        myCommand.CommandType = CommandType.StoredProcedure

        myConnection.Open()

        Dim reader As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

        Dim Productos As New List(Of Producto)

        While reader.Read()
            Dim rsProducto As New Producto
            rsProducto.Codigo = reader("CodigoProducto").ToString
            rsProducto.Nombre = reader("NombreProducto").ToString
            rsProducto.UnidadMedida = reader("UnidadMedida").ToString
            rsProducto.FechaIngreso = reader("FechaIngreso")
            rsProducto.StockActual = Convert.ToInt32(reader("StockActual"))
            rsProducto.StockMaximo = Convert.ToInt32(reader("StockMaximo"))
            rsProducto.StockMinimo = Convert.ToInt32(reader("StockMinimo"))
            rsProducto.PrecioUnitario = Convert.ToDecimal(reader("PrecioUnitario"))
            Productos.Add(rsProducto)
        End While

        reader.Close()
        myConnection.Close()

        Return Productos

    End Function

    Public Shared Function ListarProductos(ByVal strBusqueda As String) As List(Of Producto)

        Dim myConnection As New SqlConnection(ConexionDA.ObtenerCnx)
        Dim myCommand As New SqlCommand("COM_PRODUCTOS_Buscar", myConnection)
        myCommand.CommandType = CommandType.StoredProcedure

        myConnection.Open()

        Dim para As SqlParameter

        para = New SqlParameter("@strBusqueda", SqlDbType.VarChar, 100)
        para.Direction = ParameterDirection.Input
        para.Value = strBusqueda
        myCommand.Parameters.Add(para)

        Dim reader As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

        Dim Productos As New List(Of Producto)

        While reader.Read()
            Dim rsProducto As New Producto
            rsProducto.Codigo = reader("CodigoProducto").ToString
            rsProducto.Nombre = reader("NombreProducto").ToString
            rsProducto.UnidadMedida = reader("UnidadMedida").ToString
            rsProducto.FechaIngreso = reader("FechaIngreso")
            rsProducto.StockActual = Convert.ToInt32(reader("StockActual"))
            rsProducto.StockMaximo = Convert.ToInt32(reader("StockMaximo"))
            rsProducto.StockMinimo = Convert.ToInt32(reader("StockMinimo"))
            rsProducto.PrecioUnitario = Convert.ToDecimal(reader("PrecioUnitario"))
            Productos.Add(rsProducto)
        End While

        reader.Close()
        myConnection.Close()

        Return Productos

    End Function

    Public Shared Function ListarProductoxOrdenCompra(ByVal intIdOrden As Integer) As DataSet
        Dim myConnection As New SqlConnection(ConexionDA.ObtenerCnx)
        Dim myDataadapter As New SqlDataAdapter("usp_ListProductoOrdenCompraByCodigo", myConnection)
        myDataadapter.SelectCommand.CommandType = CommandType.StoredProcedure

        Dim ds As New DataSet

        Dim para As SqlParameter

        para = New SqlParameter("@intIdOrden", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = intIdOrden
        myDataadapter.SelectCommand.Parameters.Add(para)

        myDataadapter.Fill(ds, "Ordenes")

        Return ds

    End Function

    Public Shared Function ListarStockActualProducto(ByVal CodProducto As String) As DataSet
        Dim myConnection As New SqlConnection(ConexionDA.ObtenerCnx)
        Dim myDataadapter As New SqlDataAdapter("COM_Producto_ListStockActual", myConnection)
        myDataadapter.SelectCommand.CommandType = CommandType.StoredProcedure

        Dim ds As New DataSet
        Dim para As SqlParameter

        para = New SqlParameter("@CodProducto", SqlDbType.VarChar, 5)
        para.Direction = ParameterDirection.Input
        para.Value = CodProducto
        myDataadapter.SelectCommand.Parameters.Add(para)

        myDataadapter.Fill(ds, "Stock")

        Return ds
    End Function

    Public Shared Function MostrarKardex(ByVal CodProducto As String) As DataSet
        Dim myConnection As New SqlConnection(ConexionDA.ObtenerCnx)
        Dim myDataadapter As New SqlDataAdapter("COM_Producto_MostrarKardex", myConnection)
        myDataadapter.SelectCommand.CommandType = CommandType.StoredProcedure

        Dim ds As New DataSet
        Dim para As SqlParameter

        para = New SqlParameter("@CodProducto", SqlDbType.VarChar, 5)
        para.Direction = ParameterDirection.Input
        para.Value = CodProducto
        myDataadapter.SelectCommand.Parameters.Add(para)

        myDataadapter.Fill(ds, "Kardex")

        Return ds
    End Function

    Public Shared Function ActualizarStockActual(ByVal CodProducto As String, ByVal intCantidad As Integer, ByVal chMovimiento As Char) As Boolean
        Try

        
            Dim myConnection As New SqlConnection(ConexionDA.ObtenerCnx)
            Dim myCommand As New SqlCommand("COM_Producto_UpdStockActual", myConnection)
            myCommand.CommandType = CommandType.StoredProcedure

            Dim para As SqlParameter

            para = New SqlParameter("@CodProducto", SqlDbType.VarChar, 5)
            para.Direction = ParameterDirection.Input
            para.Value = CodProducto
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@intCantidad", SqlDbType.Int)
            para.Direction = ParameterDirection.Input
            para.Value = intCantidad
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@chMovimiento", SqlDbType.Char, 1)
            para.Direction = ParameterDirection.Input
            para.Value = chMovimiento
            myCommand.Parameters.Add(para)

            myConnection.Open()
            myCommand.ExecuteNonQuery()
            myConnection.Close()

            Return True
        Catch ex As Exception
            Dim E As String = ex.Message
            Return False
        End Try
    End Function

    Public Shared Function ListarProductosconProveedor() As List(Of Producto)

        Dim myConnection As New SqlConnection(ConexionDA.ObtenerCnx)
        Dim myCommand As New SqlCommand("COM_Producto_ListconProveedor", myConnection)
        myCommand.CommandType = CommandType.StoredProcedure

        myConnection.Open()

        Dim reader As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

        Dim Productos As New List(Of Producto)

        While reader.Read()
            Dim rsProducto As New Producto
            rsProducto.Codigo = reader("CodigoProducto").ToString
            rsProducto.Nombre = reader("NombreProducto").ToString
            rsProducto.UnidadMedida = reader("UnidadMedida").ToString
            rsProducto.FechaIngreso = reader("FechaIngreso")
            rsProducto.PrecioUnitario = Convert.ToDecimal(reader("PrecioUnitario"))
            Productos.Add(rsProducto)
        End While

        reader.Close()
        myConnection.Close()

        Return Productos

    End Function

    Public Shared Function ListarProductoconProveedor(ByVal intIdOrden As Integer) As DataSet
        Dim myConnection As New SqlConnection(ConexionDA.ObtenerCnx)
        Dim myDataadapter As New SqlDataAdapter("usp_ListProductoOrdenCompraByCodigo", myConnection)
        myDataadapter.SelectCommand.CommandType = CommandType.StoredProcedure

        Dim ds As New DataSet

        Dim para As SqlParameter

        para = New SqlParameter("@intIdOrden", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = intIdOrden
        myDataadapter.SelectCommand.Parameters.Add(para)

        myDataadapter.Fill(ds, "Ordenes")

        Return ds

    End Function

    Public Shared Function ObtenerStockProducto(ByVal CodProducto As String) As DataSet
        Dim myConnection As New SqlConnection(ConexionDA.ObtenerCnx)
        Dim myDataadapter As New SqlDataAdapter("COM_Producto_ObtenerStock", myConnection)
        myDataadapter.SelectCommand.CommandType = CommandType.StoredProcedure

        Dim ds As New DataSet
        Dim para As SqlParameter

        para = New SqlParameter("@strCodigo", SqlDbType.VarChar, 5)
        para.Direction = ParameterDirection.Input
        para.Value = CodProducto
        myDataadapter.SelectCommand.Parameters.Add(para)

        myDataadapter.Fill(ds, "Stock")

        Return ds
    End Function

    Public Shared Function MostrarKardexByFechas(ByVal CodProducto As String, ByVal strFechaInicio As DateTime, ByVal strFechaFin As DateTime) As DataSet
        Dim myConnection As New SqlConnection(ConexionDA.ObtenerCnx)
        Dim myDataadapter As New SqlDataAdapter("COM_Producto_MostrarKardexByFechas", myConnection)
        myDataadapter.SelectCommand.CommandType = CommandType.StoredProcedure

        Dim ds As New DataSet
        Dim para As SqlParameter

        para = New SqlParameter("@CodProducto", SqlDbType.VarChar, 5)
        para.Direction = ParameterDirection.Input
        para.Value = CodProducto
        myDataadapter.SelectCommand.Parameters.Add(para)

        para = New SqlParameter("@strFechaInicio", SqlDbType.DateTime)
        para.Direction = ParameterDirection.Input
        para.Value = strFechaInicio
        myDataadapter.SelectCommand.Parameters.Add(para)

        para = New SqlParameter("@strFechaFin", SqlDbType.DateTime)
        para.Direction = ParameterDirection.Input
        para.Value = strFechaFin
        myDataadapter.SelectCommand.Parameters.Add(para)

        myDataadapter.Fill(ds, "Kardex")

        Return ds
    End Function
End Class
